<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Member
 *
 * @author Massri
 */
class Application_Model_Member {

    //put your code here
    private $_db;

    function __construct() {
        $registry = Zend_Registry::getInstance();
        $this->_db = $registry->database;
    }

    /*
     * $member = array(
     * member_code,member_name,pin,sponsor_id, member_type_id
     * )
     */

    public function changePass($memberId, $oldPass, $newPass){
        $this->_db->beginTransaction();

        try {

            //Cek Current Pass
            $sql = "select pin from members where member_id = $memberId";
            $res = $this->_db->fetchRow($sql);
            $currPass = $res['pin'];
            
            if ($oldPass == $currPass){
                
                $data = array(
                    'pin'   => $newPass
                );

                $where = array(
                    'member_id = ?' => $memberId
                );

                $res = $this->_db->update('members', $data, $where);

                $status = array(
                        'code'  => '0',
                        'msg'   => 'Ganti Password berhasil',
                        'memberId'  => $memberId
                    );

                $this->_db->commit();

            }else{
                $status = array(
                        'code'  => '1',
                        'msg'   => 'Password Lama tidak cocok.'
                    );
            }

            return $status;

        }catch (Exception $e) {
            $this->_db->rollBack();

            $status = array(
                'code' => '1',
                'msg' => 'Ganti Password gagal. '.$e
            );

            return $status;
        }
    }

    public function isExistMember($contMobile, $contKtp, $accBankNo, $memberId = "", $memberCode = ""){
            $sql1 = "select count(1) as num from member_contact where mobile_no = '$contMobile' and contact_type_id = 1";

            if ($memberId != ""){
                $sql1 .= " and member_id != $memberId ";
            }


            
            $res1 = $this->_db->fetchRow($sql1);
            $numHp = $res1['num'];

            $sql2 = "select count(1) as num from member_contact where no_ktp = '$contKtp' and contact_type_id = 1";

            if ($memberId != ""){
                $sql2 .= " and member_id != $memberId ";
            }

            $res2 = $this->_db->fetchRow($sql2);
            $numKtp = $res2['num'];


            $sql3 = "select count(1) as num from accounts where bank_account_no = '$accBankNo'";

            if ($memberCode != ""){
                $sql3 .= " and account_num != '$memberCode' ";
            }

            

            $res3 = $this->_db->fetchRow($sql3);
            $numRek = $res3['num'];

            $result = array(
                        "hp"    => $numHp,
                        "ktp"   => $numKtp,
                        "rek"   => $numRek
                        );

            return $result;
    }

    public function addMember($member, $account, $contact, $address, $ahliWaris) {

        $this->_db->beginTransaction();

        try {

            /*==========================================
             *  MEMBERS  
             ============================================*/
            
            /* 
             * 
             */
            
            // Get Matrik Level    
            /*
            $sqlMatrikLvl = "select getLevelMatrik(10) as matrikLevelId ";
            $res = $this->_db->fetchRow($sqlMatrikLvl);            
            $matrikLevelId = $res['matrikLevelId'];
            
            
            $sqlMatrik = "select getMatrikId(10) as matrikId ";
            $res2 = $this->_db->fetchRow($sqlMatrik);
            $matrikId = $res2['matrikId'];
            */
            
            $sqlMatrikLvl = "select getMatrikId2(". $member['sponsor_id'] .") as matrikLevelId ";
            $res = $this->_db->fetchRow($sqlMatrikLvl); 
            $matrik = explode('|', $res['matrikLevelId']);
            $matrikLevelId = $matrik[1];
            $matrikId       = $matrik[0];

            // Get Treenary
            /*
            $sqlTL = "select getLevelMatrik(3) as treeLevelId";
            $l  = $this->_db->fetchRow($sqlTL);
            $treenaryLevelId = 0;//$l['treeLevelId'];

            $sqlTI = "select getMatrikId(3) as treeId";
            $t  = $this->_db->fetchRow($sqlTI);
            $treenaryId = $t['treeId'];
            */
            
            $m = array_merge($member, array(
                'matrik_id'     => $matrikId,
                'matrik_level'  => $matrikLevelId,
                'member_status' => 'ACTIVE',
                'treenary_id'   => $treenaryId,
                'treenary_level' => $treenaryLevelId
            ));
            
            $res = $this->_db->insert('members', $m);
            $memberId = $this->_db->lastInsertId();
            
            if (!$res) {
                $status = array(
                    'code' => '1',
                    'msg' => 'Pembuatan member Gagal'
                );

                $this->_db->rollBack();
                return $status;
            }
            
            
            /*==========================================
             *  CONTACT 
             ============================================*/
            
            $c = array_merge($contact, array('member_id' => $memberId));

            $res = $this->_db->insert('member_contact', $c);
            $memberContactId = $this->_db->lastInsertId();
            
            if (!$res) {
                $status = array(
                    'code' => '2',
                    'msg' => 'Pembuatan Kontak Gagal'
                );

                $this->_db->rollBack();
                return $status;
            }


            /*==========================================
             *  AHLI WARIS
             ============================================*/

            if ($ahliWaris['contact_name'] != ""){  //Jika diisi

                $w = array_merge($ahliWaris, array('member_id' => $memberId));

                $res = $this->_db->insert('member_contact', $w);
                $warisContactId = $this->_db->lastInsertId();

                if (!$res) {
                    $status = array(
                        'code' => '2',
                        'msg' => 'Pembuatan Kontak Ahli Waris Gagal'
                    );

                    $this->_db->rollBack();
                    return $status;
                }
            }
            
            /*==========================================
             *  CONTACT ADDRESS 
             ============================================*/
            
            $a = array_merge($address, array('member_contact_id' => $memberContactId));
            
            $contactAddrId = $this->_db->insert('contact_address',$a);
            
            if (!$contactAddrId) {
                $status = array(
                    'code' => '2',
                    'msg' => 'Pembuatan Alamat Kontak  Gagal'
                );

                $this->_db->rollBack();
                return $status;
            }

            /*==========================================
             *  ACCOUNTS
             ============================================*/

            $ac = array_merge($account, array('member_id' => $memberId));
            $res = $this->_db->insert('accounts',$ac);
            $memberAccountId = $this->_db->lastInsertId();

            if (!$res) {
                $status = array(
                    'code' => '2',
                    'msg' => 'Pembuatan Akun Gagal'
                );

                $this->_db->rollBack();

                return $status;
            }

            /*==========================================
             *  TRANSACTION
             ============================================*/

            $spParams = array($memberId, $member['member_code']);
            $stmt = $this->_db->query("CALL RealTime_After_AddMember(?,?)", $spParams);

            if (!$stmt) {
                $status = array(
                    'code' => '2',
                    'msg' => 'Pembuatan Transaksi Gagal'
                );

                $this->_db->rollBack();

                return $status;
            }

            $stmt->closeCursor();


            // Update SN
            $data = array(
                'sn_status' => 1
            );

            $where = array(
                'sn_code = ?' => $member['member_code']
            );

            $res = $this->_db->update('sn', $data, $where);
            

            $status = array(
                'code' => '0',
                'msg' => 'Pendaftaran member Sukses.'
            );

            $this->_db->commit();

            return $status;
            
        } catch (Exception $e) {
            $this->_db->rollBack();
            
            $status = array(
                'code' => '3',
                'msg' => 'Pendaftaran gagal. '.$e
            );

            return $status;
        }
    }


    public function editMember($member, $memberWhere, $account, $accountWhere, $contact, $contactWhere,
                                $address, $addressWhere, $ahliWaris, $warisWhere) {

        $this->_db->beginTransaction();

        try {

            /*==========================================
             *  MEMBERS
             ============================================*/

            /*
             *
             */


            $resM = $this->_db->update('members', $member, $memberWhere);

            /*
            if (!$resM) {
                $status = array(
                    'code' => '1',
                    'msg' => 'Update member Gagal: '. $resM
                );

                $this->_db->rollBack();
                return $status;
            }
             *
             */


            /*==========================================
             *  CONTACT
             ============================================*/


            $resMC = $this->_db->update('member_contact', $contact, $contactWhere);

            /*
            if (!$resMC) {
                $status = array(
                    'code' => '2',
                    'msg' => 'Update Kontak Gagal'
                );

                $this->_db->rollBack();
                return $status;
            }
             * 
             */


            /*==========================================
             *  AHLI WARIS
             ============================================*/

            if (count($warisWhere) > 0){  //Jika diisi

                $resW = $this->_db->update('member_contact', $ahliWaris, $warisWhere);

                
            }else{
                
                $w = array_merge($ahliWaris, array('member_id' => $memberWhere["member_id = ?"], 'contact_type_id' => 2));

                $resW = $this->_db->insert('member_contact', $w);

                if (!$resW) {
                    $status = array(
                        'code' => '2',
                        'msg' => 'Update Kontak Ahli Waris Gagal'
                    );

                    $this->_db->rollBack();
                    return $status;
                }
                
            }

            
            /*==========================================
             *  CONTACT ADDRESS
             ============================================*/

            $resCA = $this->_db->update('contact_address', $address, $addressWhere);

            /*
            if (!$resCA) {
                $status = array(
                    'code' => '2',
                    'msg' => 'Update Alamat Kontak Gagal'
                );

                $this->_db->rollBack();
                return $status;
            }
             * 
             */


            /*==========================================
             *  ACCOUNTS
             ============================================*/
            
            $resAcc = $this->_db->update('accounts', $account, $accountWhere);

            /*
            if (!$resAcc) {
                $status = array(
                    'code' => '2',
                    'msg' => 'Pembuatan Akun Gagal'
                );

                $this->_db->rollBack();

                return $status;
            }
             * 
             */

            
            $status = array(
                'code' => '0',
                'msg' => 'Update data member sukses.'
            );

            $this->_db->commit();

            return $status;

        } catch (Exception $e) {
            $this->_db->rollBack();

            $status = array(
                'code' => '3',
                'msg' => 'Update data member gagal. '.$e
            );

            return $status;
        }
    }


    public function getMemberTypeList() {

        $select = $this->_db->select();
        $select->from('member_types');
        $select->where( " member_type_id > 1 " );
        $select->order( 'member_type_name' );

        $stmt = $select->query();
        $result = $stmt->fetchAll();

        return $result;

    }

    public function getContactTypeList() {

        $select = $this->_db->select();
        $select->from('contact_types');
        $select->order( 'contact_type_name' );

        $stmt = $select->query();
        $result = $stmt->fetchAll();

        return $result;

    }

    function getMember($sBy, $key, $start, $limit) {
        //$data = array();

        $select = $this->_db->select();
        $select->from('members');

        if ($sBy != ""){
            $select->where( $sBy ." like '%$key%' ");
            $select->order( $sBy );
        }

        if ($limit != ""){
            $select->limit($limit, $start);
        }

        //$sql = $select->__toString();
        //echo "$sql\n";


        $stmt = $select->query();
        $result = $stmt->fetchAll();

        return $result;
    }


    function getMemberContact($sBy, $key) {

        $sql = "select * from member_contact ";

        if ($sBy != ""){
            $sql .= " where $sBy = '$key' ";
        }

        $result = $this->_db->fetchAll($sql);

        return $result;
    }

    public function getMemberByClause($clause, $start,$limit){
        $select = $this->_db->select();
        $select->from('members')
                ->where($clause)
                ->limit($limit,$start);

        $stmt = $select->query();
        $result = $stmt->fetchAll();

        return $result;

    }
    
    function getSponsorNetwork($sBy, $key, $parentCode) {

            /*
            $select = $this->_db->select();
            $select->from('members');
            $select->where( $sBy ." = '$key' and member_type_id = 2 ");
            $select->order( 'matrik_level' );
            $select->order( 'member_id' );
            
            //$sql = $select->__toString();
            //echo "$sql\n";

            $stmt = $select->query();
            $result = $stmt->fetchAll();
             * 
             */

            $sql = "select m.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                    from members m, member_contact mc, contact_address ca
                    where m.$sBy  = '$key' and member_type_id = 2 and
                    mc.member_id = m.member_id and mc.contact_type_id = 1
                    and ca.member_contact_id = mc.member_contact_id order by matrik_level, m.member_id
                ";

            $result = $this->_db->fetchAll($sql);

            $num = count($result);

            $this->tmpTree = "";
            $sponsor1 = "";
            $sponsor2 = "";
            $sponsor3 = "";
            $sponsor4 = "";
            $sponsor5 = "";
            
            $cnt = 0;

            if ($num > 0){

                $this->tmpTree .= "<ul>\n";

                for($k = 0;$k<$num;$k++) {
                        $cnt++;
                        $this->tmpTree .= "[SPONSOR1]";
                        $sponsor[] = "<li title='<center>".strtoupper($result[$k]['member_name']."<br>".$result[$k]['mobile_no']."<br>".$result[$k]['addr'])."</center>' data-actor='".$result[$k]['member_id']."' class='small' ><br>".str_replace('CBR','CBR<br>',str_replace('NIK','NIK<br>',$result[$k]['member_code']));

                        /*
                        $select2 = $this->_db->select();
                        $select2->from('members');
                        $select2->where( $sBy ." = '".$result[$k]['sponsor_id']."' and member_type_id = 2 ");
                        $select2->order( 'matrik_level' );
                        $select2->order( 'member_id' );
                        
                        $stmt2 = $select2->query();
                        $result2 = $stmt2->fetchAll();
                         * 
                         */

                        $sql2 = "select m.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                                from members m, member_contact mc, contact_address ca
                                where m.$sBy  = '".$result[$k]['sponsor_id']."' and member_type_id = 2 and
                                mc.member_id = m.member_id and mc.contact_type_id = 1
                                and ca.member_contact_id = mc.member_contact_id order by matrik_level, m.member_id
                            ";

                        $result2 = $this->_db->fetchAll($sql2);

                        $num2 = count($result2);

                        if ($num2 > 0){
                            $this->tmpTree .= "<ul>\n";

                            for($k2 = 0;$k2<$num2;$k2++) {
                                    $cnt++;
                                    $this->tmpTree .= "[SPONSOR2]";
                                    $sponsor[] = "<li title='<center>".strtoupper($result2[$k2]['member_name']."<br>".$result2[$k2]['mobile_no']."<br>".$result2[$k2]['addr']."</center>' data-actor='".$result2[$k2]['member_id'])."' class='small'><br>".str_replace('CBR','CBR<br>',str_replace('NIK','NIK<br>',$result2[$k2]['member_code']));

                                    /*
                                    $select3 = $this->_db->select();
                                    $select3->from('members');
                                    $select3->where( $sBy ." = '".$result2[$k2]['sponsor_id']."' and member_type_id = 2 ");
                                    $select3->order( 'matrik_level' );
                                    $select3->order( 'member_id' );

                                    $stmt3 = $select3->query();
                                    $result3 = $stmt3->fetchAll();
                                     * 
                                     */

                                    $sql3 = "select m.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                                            from members m, member_contact mc, contact_address ca
                                            where m.$sBy  = '".$result2[$k2]['sponsor_id']."' and member_type_id = 2 and
                                            mc.member_id = m.member_id and mc.contact_type_id = 1
                                            and ca.member_contact_id = mc.member_contact_id order by matrik_level, m.member_id
                                        ";

                                    $result3 = $this->_db->fetchAll($sql3);

                                    $num3 = count($result3);

                                    if ($num3 > 0){
                                        $this->tmpTree .= "<ul>\n";

                                        for($k3 = 0;$k3<$num3;$k3++) {
                                                $cnt++;
                                                $this->tmpTree .= "[SPONSOR3]";
                                                $sponsor[] = "<li title='<center>".strtoupper($result3[$k3]['member_name']."<br>".$result3[$k3]['mobile_no']."<br>".$result3[$k3]['addr'])."</center>' data-actor='".$result3[$k3]['member_id']."' class='small'><br>".str_replace('CBR','CBR<br>',str_replace('NIK','NIK<br>',$result3[$k3]['member_code']));

                                                /*
                                                $select4 = $this->_db->select();
                                                $select4->from('members');
                                                $select4->where( $sBy ." = '".$result3[$k3]['sponsor_id']."' and member_type_id = 2 ");
                                                $select4->order( 'matrik_level' );
                                                $select4->order( 'member_id' );

                                                $stmt4 = $select4->query();
                                                $result4 = $stmt4->fetchAll();
                                                 * 
                                                 */

                                                $sql4 = "select m.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                                                        from members m, member_contact mc, contact_address ca
                                                        where m.$sBy  = '".$result3[$k3]['sponsor_id']."' and member_type_id = 2 and
                                                        mc.member_id = m.member_id and mc.contact_type_id = 1
                                                        and ca.member_contact_id = mc.member_contact_id order by matrik_level, m.member_id
                                                    ";

                                                $result4 = $this->_db->fetchAll($sql4);

                                                $num4 = count($result4);

                                                if ($num4 > 0){
                                                    $this->tmpTree .= "<ul>\n";

                                                    for($k4 = 0;$k4<$num4;$k4++) {
                                                            $cnt++;
                                                            $this->tmpTree .= "[SPONSOR4]";
                                                            $sponsor[] = "<li title='<center>".strtoupper($result4[$k4]['member_name']."<br>".$result4[$k4]['mobile_no']."<br>".$result4[$k4]['addr'])."</center>' data-actor='".$result4[$k4]['member_id']."' class='small'><br>".str_replace('CBR','CBR<br>',str_replace('NIK','NIK<br>',$result4[$k4]['member_code']));

                                                            /*
                                                            $select5 = $this->_db->select();
                                                            $select5->from('members');
                                                            $select5->where( $sBy ." = '".$result4[$k4]['sponsor_id']."' and member_type_id = 2 ");
                                                            $select5->order( 'matrik_level' );
                                                            $select5->order( 'member_id' );

                                                            $stmt5 = $select5->query();
                                                            $result5 = $stmt5->fetchAll();
                                                             * 
                                                             */

                                                            $sql5 = "select m.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                                                                    from members m, member_contact mc, contact_address ca
                                                                    where m.$sBy  = '".$result4[$k4]['sponsor_id']."' and member_type_id = 2 and
                                                                    mc.member_id = m.member_id and mc.contact_type_id = 1
                                                                    and ca.member_contact_id = mc.member_contact_id order by matrik_level, m.member_id
                                                                ";

                                                            $result5 = $this->_db->fetchAll($sql5);

                                                            $num5 = count($result5);

                                                            if ($num5 > 0){
                                                                $this->tmpTree .= "<ul>\n";

                                                                for($k5 = 0;$k5<$num5;$k5++) {
                                                                        $this->tmpTree .= "[SPONSOR5]";
                                                                        $sponsor[] = "<li title='<center>".strtoupper($result5[$k5]['member_name']."<br>".$result5[$k5]['mobile_no']."<br>".$result5[$k5]['addr'])."</center>' data-actor='".$result5[$k5]['member_id']."' class='small'><br>".str_replace('CBR','CBR<br>',str_replace('NIK','NIK<br>',$result5[$k5]['member_code']));
                                                                }

                                                                $this->tmpTree .= "</ul>\n";
                                                            }
                                                    }

                                                    $this->tmpTree .= "</ul>\n";
                                                }
                                        }

                                        $this->tmpTree .= "</ul>\n";
                                    }

                            }

                            $this->tmpTree .= "</ul>\n";
                        }
                }

                $this->tmpTree .= "</ul>\n";

            }

            if ($cnt > 0){
                krsort($sponsor);
                $z = 1;
                foreach ($sponsor as $key => $val) {
                    $this->tmpTree = str_replace('[SPONSOR'.$z.']', $val, $this->tmpTree);
                    $z++;
                }
            }

            $resTree = array("tree" => $this->tmpTree);

            return $resTree;

    }

    function getMemberNetwork($sBy, $key, $parentCode) {


            /*$select = $this->_db->select();
            $select->from('members');
            $select->where( $sBy ." = '$key' and member_type_id = 2 ");
            $select->order( 'matrik_level' );
            $select->order( 'member_id' );
             * 
             */
            //$select->order( 'bin_pos' );


            //$sql = $select->__toString();
            //echo "$sql\n";
            
            //$stmt = $select->query();
            //$result = $stmt->fetchAll();

            
            $sql = "SELECT  level, hi.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                    FROM (
                    SELECT  CPriorMatrik(member_id) AS member_id, @level AS level
                    FROM (
                    SELECT  @start_with := $key, @id := @start_with,@level := 0
                    ) vars, members
                    WHERE   @id IS NOT NULL
                    ) ho
                    JOIN    members hi
                    ON      hi.member_id = ho.member_id
                    JOIN member_contact mc
                    ON hi.member_id = mc.member_id
                    JOIN contact_address ca
                    ON mc.member_contact_id = ca.member_contact_id
                    WHERE mc.contact_type_id = 1 and level = 1";

            $result = $this->_db->fetchAll($sql);

            $num = count($result);

            $this->tmpTree = "";

            if ($num > 0){

                $this->tmpTree .= "<ul id='ul-matrix' class='hide'>\n";

                for($k = 0;$k<$num;$k++) {

                        $this->tmpTree .= "<li title='<center>".strtoupper($result[$k]['member_name']."<br>".$result[$k]['mobile_no']."<br>".$result[$k]['addr'])."</center>' data-actor='".$result[$k]['member_id']."' class='small' ><br>".str_replace('CBR','CBR<br>',str_replace('NIK','NIK<br>',$result[$k]['member_code']));
                        //$this->tmpTree .= "<img src='".Zend_Controller_Front::getInstance()->getBaseUrl()."/img/ava-nopict-small.jpg'/>\n";

                        /*
                        $select4 = $this->_db->select();
                        $select4->from('members');
                        $select4->where( $sBy ." = '".$result[$k]['member_id']."' and member_type_id = 2 ");
                        $select4->order( 'matrik_level' );
                        $select4->order( 'member_id' );
                        //$select4->order( 'bin_pos' );

                        $stmt4 = $select4->query();
                        $result4 = $stmt4->fetchAll();
                        $num4 = count($result4);

                        if ($num4 > 0){
                            $this->tmpTree .= "<ul>\n";

                            for($k4 = 0;$k4<$num4;$k4++) {                                
                                    $this->tmpTree .= "<li data-actor='".$result4[$k4]['member_id']."' class='small'><br>".str_replace('CBR','CBR<br>',$result4[$k4]['member_code']);
                                    //$this->tmpTree .= "<img src='".Zend_Controller_Front::getInstance()->getBaseUrl()."/img/ava-nopict-small.jpg'/></li>\n";

                            }
                             

                            $this->tmpTree .= "</ul>\n";
                        }
                        */
                }

                $this->tmpTree .= "</ul>\n";

            }


            $resTree = array("tree" => $this->tmpTree);

            return $resTree;

    }

    function getMemberTreenary($sBy, $key, $parentCode) {

            /*
            $select = $this->_db->select();
            $select->from('members');
            $select->where( $sBy ." = '$key' and member_type_id = 2 ");
            $select->order( 'treenary_level' );
            $select->order( 'member_id' );
            //$select->order( 'bin_pos' );


            //$sql = $select->__toString();
            //echo "$sql\n";

            $stmt = $select->query();
            $result = $stmt->fetchAll();
             * 
             */

            $sql = "select m.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                    from members m, member_contact mc, contact_address ca
                    where $sBy  = '$key' and member_type_id = 2 and
                    mc.member_id = m.member_id and mc.contact_type_id = 1
                    and ca.member_contact_id = mc.member_contact_id order by treenary_level, member_id
                ";



            $result = $this->_db->fetchAll($sql);

            $num = count($result);

            $this->tmpTree = "";

            if ($num > 0){

                $this->tmpTree .= "<ul>\n";

                for($k = 0;$k<$num;$k++) {

                        $this->tmpTree .= "<li title='<center>".strtoupper($result[$k]['member_name']."<br>".$result[$k]['mobile_no']."<br>".$result[$k]['addr'])."</center>' data-actor='".$result[$k]['member_id']."' class='small' ><br>".str_replace('CBR','CBR<br>',str_replace('NIK','NIK<br>',$result[$k]['member_code']));

                        /*
                        $select2 = $this->_db->select();
                        $select2->from('members');
                        $select2->where( $sBy ." = '".$result[$k]['member_id']."' and member_type_id = 2 ");
                        $select2->order( 'treenary_level' );
                        $select2->order( 'member_id' );
                        
                        $stmt2 = $select2->query();
                        $result2 = $stmt2->fetchAll();
                         * 
                         */

                        $sql2 = "select m.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                                from members m, member_contact mc, contact_address ca
                                where $sBy  = '".$result[$k]['member_id']."' and member_type_id = 2 and
                                mc.member_id = m.member_id and mc.contact_type_id = 1
                                and ca.member_contact_id = mc.member_contact_id order by treenary_level, member_id
                            ";

                        $result2 = $this->_db->fetchAll($sql2);

                        $num2 = count($result2);

                        if ($num2 > 0){
                            $this->tmpTree .= "<ul>\n";

                            for($k2 = 0;$k2<$num2;$k2++) {
                                    $this->tmpTree .= "<li title='<center>".strtoupper($result2[$k2]['member_name']."<br>".$result2[$k2]['mobile_no']."<br>".$result2[$k2]['addr'])."</center>' data-actor='".$result2[$k2]['member_id']."' class='small'><br>".str_replace('CBR','CBR<br>',str_replace('NIK','NIK<br>',$result2[$k2]['member_code']));
                                    /*
                                    $select3 = $this->_db->select();
                                    $select3->from('members');
                                    $select3->where( $sBy ." = '".$result2[$k2]['member_id']."' and member_type_id = 2 ");
                                    $select3->order( 'treenary_level' );
                                    $select3->order( 'member_id' );

                                    $stmt3 = $select3->query();
                                    $result3 = $stmt3->fetchAll();
                                     * 
                                     */

                                    $sql3 = "select m.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                                            from members m, member_contact mc, contact_address ca
                                            where $sBy  = '".$result2[$k2]['member_id']."' and member_type_id = 2 and
                                            mc.member_id = m.member_id and mc.contact_type_id = 1
                                            and ca.member_contact_id = mc.member_contact_id order by treenary_level, member_id
                                        ";

                                    $result3 = $this->_db->fetchAll($sql3);
                                    $num3 = count($result3);

                                    if ($num3 > 0){
                                        $this->tmpTree .= "<ul>\n";

                                        for($k3 = 0;$k3<$num3;$k3++) {
                                                $this->tmpTree .= "<li title='<center>".strtoupper($result3[$k3]['member_name']."<br>".$result3[$k3]['mobile_no']."<br>".$result3[$k3]['addr'])."</center>' data-actor='".$result3[$k3]['member_id']."' class='small'><br>".str_replace('CBR','CBR<br>',str_replace('NIK','NIK<br>',$result3[$k3]['member_code']));

                                        }


                                        $this->tmpTree .= "</ul>\n";
                                    }

                            }


                            $this->tmpTree .= "</ul>\n";
                        }
                }

                $this->tmpTree .= "</ul>\n";

            }


            $resTree = array("tree" => $this->tmpTree);

            return $resTree;

    }


    function getAhliWaris($memberID) {

        $sql = 'select * from member_contact where member_id = '.$memberID.' and contact_type_id = 2';

        $result = $this->_db->fetchRow($sql);

        return $result;
    }

    function updateAhliWaris($warisContactId, $dataAhliWaris){

        $this->_db->beginTransaction();

        try {

            $where = array(
                'member_contact_id = ?' => $warisContactId
            );

            $res = $this->_db->update('member_contact', $dataAhliWaris, $where);

            $status = array(
                    'code'  => '0',
                    'msg'   => 'Ubah Ahli Waris berhasil'
                );

            $this->_db->commit();

            return $status;

        }catch (Exception $e) {
            $this->_db->rollBack();

            $status = array(
                'code' => '1',
                'msg' => 'Ganti Password gagal. '.$e
            );

            return $status;
        }
    }


    function getMemberDetail($memberId){
        
        $sql = "select x.*, sp.member_code as sponsor_code, sp.member_name as sponsor_name from (
                select m.member_id, m.member_code, m.member_name, m.pin, c.contact_name, c.email, c.mobile_no,
                c.phone, addr1, addr2, addr3, addr4, addr5, country,
                account_name, bank_account_no, bank_city, bank_name, member_type_name,
                m.sponsor_id,
                m.class_reward,
                m.class_reward_stat,
                m.class_reward_note,
                m.is_qualified_int_share,
                m.matrik_id,
                c.member_contact_id,
                a.contact_address_id,
                c.no_ktp
                from members m, member_contact c, contact_address a, accounts ac, member_types mt
                where m.member_id = $memberId
                and c.member_id = m.member_id and c.contact_type_id = 1
                and a.member_contact_id = c.member_contact_id
                and ac.member_id = m.member_id and mt.member_type_id = m.member_type_id
                ) x left outer join members sp on sp.member_id = x.sponsor_id";

        $res = $this->_db->fetchRow($sql);

        $sqlW = "select w.contact_name as waris_name,
                w.email as waris_email,
                w.mobile_no as waris_mobile,
                w.phone as waris_phone,
                w.no_ktp as waris_ktp,
                w.member_contact_id as member_contact_idw
                from member_contact w
                where w.member_id = $memberId and w.contact_type_id = 2";
        
        $resW = $this->_db->fetchRow($sqlW);

        
        if (is_array($resW)){
            return array_merge($res, $resW);
        }else return $res;

        
    }


    function getInfoNetwork($memberId, $parentCode, $level, $start, $limit) {

        if ($parentCode != ""){
            $sql = "select member_id
                    from members where member_code = '$parentCode' and member_type_id = 2";

            $result = $this->_db->fetchRow($sql);
            $memberId = $result['member_id'];
        }



        if ($level != ""){
            $sql = "select count(1) as total
                    from members where matrik_level = $level and member_type_id = 2";
            
        }else if ($memberId != "") {
            $sql = "select count(1) as total
                    from members where matrik_id = $memberId and member_type_id = 2";
        }


        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];

        $data = array($rows);

        if ($rows > 0){

            if ($level != ""){
                $sql = "select member_id, member_code, matrik_level, reg_dtm
                        from members where matrik_level = $level and member_type_id = 2
                        order by matrik_level, member_id";

            }else{

                $sql = "select member_id, member_code, matrik_level, reg_dtm
                        from members where matrik_id = $memberId and member_type_id = 2
                        order by matrik_level, member_id";
            }

            if ($limit != ""){
                $sql .= " limit $start, $limit";
            }

            $result = $this->_db->fetchAll($sql);
        }

        array_push($data, $result);


        return $data;
    }

    function getNewMembers($num){

        $sql = "select * from (select member_name, member_code
                        from members where member_type_id > 1 order by member_id desc) x limit $num";


        $result = $this->_db->fetchAll($sql);


        $tmp = "";
        $no = 1;
        foreach ($result as $res){
            $tmp .= $no.". ".$res['member_name']." (".$res['member_code'].")<br>";
            $no++;
        }

        return $tmp;
       
    }

    function getRewardMembers($num){

        $sql = "select * from (select member_name, member_code
                        from members where class_reward is not null and class_reward_stat > 0 order by member_id desc) x limit $num";


        $result = $this->_db->fetchAll($sql);


        $tmp = "";
        $no = 1;
        foreach ($result as $res){
            $tmp .= $no.". ".$res['member_name']." (".$res['member_code'].")<br>";
            $no++;
        }

        return $tmp;

    }


    function getPerkembanganMember($date1, $date2, $start, $limit){

        if ($limit != ""){
            $sql = "select count(1) as total from( ";
   

            $sql .= " select date_format(reg_dtm,'%Y%m%d') period, 4 as jenis
                    from members
                    where STR_TO_DATE(DATE_FORMAT(reg_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";


            $sql .= " group by period, jenis) a"; // and a.id_reff1=th.user_ref";

            $result = $this->_db->fetchRow($sql);
            $rows = $result['total'];

            $data = array($rows);
        }



        $sql = "select period, FORMAT(total_bonus,0) as total_bonus,
                FORMAT(sisa_bonus,0) as sisa_bonus
                from ( ";

        $sql .= "select date_format(reg_dtm,'%Y%m%d') as period, count(member_id) as total_bonus, max(0) as sisa_bonus
                from members
                where STR_TO_DATE(DATE_FORMAT(reg_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                group by date_format(reg_dtm,'%Y%m%d')";
       

        $sql .=  ") xx where total_bonus > 0 order by period desc ";

        if ($limit != ""){
            $sql .=  " limit $start, $limit ";
        }

        $result = $this->_db->fetchAll($sql);

        if ($limit != ""){
            array_push($data, $result);
        }else{
            $data = $result;
        }

        return $data;
    }


    function getMemberDownline($parentId) {

        $sql = "SELECT  level, hi.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                FROM (
                SELECT  CPriorMatrik(member_id) AS member_id, @level AS level
                FROM (
                SELECT  @start_with := $parentId, @id := @start_with,@level := 0
                ) vars, members
                WHERE   @id IS NOT NULL
                ) ho
                JOIN    members hi
                ON      hi.member_id = ho.member_id
                JOIN member_contact mc
                ON hi.member_id = mc.member_id
                JOIN contact_address ca
                ON mc.member_contact_id = ca.member_contact_id
                WHERE mc.contact_type_id = 1 ";

        $result = $this->_db->fetchAll($sql);

        return $result;
    }
}
